﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;
using System.Windows;

namespace QLKS.Models
{
    #region <Class DataLib> 'Giao Tiếp DataBase Sql'
    static public class DataProviderModel
    {
        #region Property
        static public SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Data\\QLKS.mdf;Integrated Security=True");
        //Data Source=.\\SQLEXPRESS;AttachDbFilename=Data\\QLKS.mdf;Integrated Security=True;User Instance=True");
        static public DataSet ds = new DataSet();
        static public string ServerName { get { return conn.DataSource; } }
        static public string DatabaseName
        {
            get
            {
                string[] Data = conn.ConnectionString.Split(';')[1].Split('\\');
                return Data[Data.Length - 1];;
            }
        }
        #endregion

        #region Melthod
        //GetColumns Table
        static public DataTable CreateColumns(string TableName, string filter)
        {
            DataTable tb = DataProviderModel.QueryResults("select * from " + TableName + " where 1=0").Tables[0];
            foreach (string cl in filter.Split(',',';'))
                if(cl.Trim() != "")
                    tb.Columns.Remove(cl);
            return tb;
        }

        //Create Columns
        static private string GetColumns(DataTable _tb, string filter) //Lọc lại column của table
        {
            DataTable tb = _tb.Copy();
            string result = string.Empty;
            foreach (string cl in filter.Split(',', ';'))
                if (filter.Trim() != "")
                    tb.Columns.Remove(cl);
            foreach (DataColumn c in tb.Columns)
                result += c.ColumnName + ",";
            if (result.Length > 0)
                result = result.Substring(0, result.Length - 1);
            return result;
        }

        //insert
        static public int Insert(DataTable tb, string TableName, string filter)
        {
            int n = 0;
            DataTable tbcolumns = DataProviderModel.CreateColumns(TableName, "");
            string columns = GetColumns(tbcolumns, filter);
            foreach (DataRow row in tb.Rows)
            {
                conn.Open();
                string query = "INSERT INTO " + TableName + " (" + columns + ")VALUES(@" + columns.Replace(",", ",@") + ")";
                SqlCommand cmd = new SqlCommand(query, conn);
                foreach (string cl in columns.Split(',', ';'))
                {
                    SqlParameter value = new SqlParameter("@" + cl,row[cl]);
                    cmd.Parameters.Add(value);
                }
                n += cmd.ExecuteNonQuery();
                conn.Close();
            }
            return n;
        }

        //Create columns Update
        static private string CreateColumnUpdate(string columns)
        {
            string result = string.Empty;
            foreach (string cl in columns.Split(',', ';'))
                result += cl + " = @" + cl + ",";
            if (result.Length > 0)
                result = result.Substring(0, result.Length - 1);
            return result;
        }

        //Update
        static public int Update(DataTable tb, string TableName, string filter)
        {
            int n = 0;
            DataTable tbcolumns = DataProviderModel.CreateColumns(TableName, "");
            string columns = GetColumns(tbcolumns, "");
            foreach (DataRow row in tb.Rows)
            {
                string query = "UPDATE " + TableName + " SET " + CreateColumnUpdate(GetColumns(tbcolumns, filter)) + " WHERE " + CreateColumnUpdate(filter);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                foreach (string cl in columns.Split(',', ';'))
                {
                    SqlParameter value = new SqlParameter("@" + cl, row[cl]);
                    cmd.Parameters.Add(value);
                }
                n += cmd.ExecuteNonQuery();
                conn.Close();
            }
            return n;
        }

        //Delete
        static public int Detele(DataTable tb, string TableName)
        {
            int n = 0;
            string columns = GetColumns(tb, "");
            foreach (DataRow row in tb.Rows)
            {
                string query = "DELETE " + TableName +" WHERE " + CreateColumnUpdate(columns);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                foreach (string cl in columns.Split(',', ';'))
                {
                    SqlParameter value = new SqlParameter("@" + cl, row[cl]);
                    cmd.Parameters.Add(value);
                }
                n += cmd.ExecuteNonQuery();
                conn.Close();
            }
            return n;
        }

        //Query Result
        static public DataSet QueryResults(string query)
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(query, conn);
                SqlDataAdapter adp = new SqlDataAdapter(comm);
                conn.Close();
                adp.Fill(ds);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, "QLKS", MessageBoxButton.OK, MessageBoxImage.Error);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            if (ds.Tables.Count <= 0)
                return new DataSet();
            return ds;
        }

        //Run Execute
        static public void RunExecute(string query)
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(query, conn);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "QLKS", MessageBoxButton.OK, MessageBoxImage.Error);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        //ScalarExecute
        static public object ScalarExecute(string query)
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(query, conn);
                object n = comm.ExecuteScalar();
                conn.Close();
                return n;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "QLKS", MessageBoxButton.OK, MessageBoxImage.Error);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return null;
        }

        //LeftPad
        static public string LeftPad(string text, int len, string s)
        {
            for (int i = 0; i < len; i++)
                text = s + text;
            return text;
        }

        //Open
        static public bool Open()
        {
            try
            {
                conn.Open();                
            }
            catch { return false; }
            return true;
        }

        //is open
        static public bool IsOpen { get { return conn.State == ConnectionState.Open; } }
        static public bool IsClosed { get { return conn.State == ConnectionState.Closed; } }
        //close
        static public bool Close()
        {
            try
            {
                conn.Close();
            }
            catch { return false; }
            return true;
        }

        //ConnectionString
        static public string ConnectionString
        {
            get { return conn.ConnectionString; }
            set { conn.ConnectionString = value; }
        }

        //Send mail
        static private void SendMail(string username, string password, string from, string to, string subject, string body)
        {
            System.Net.Mail.MailMessage MyMailMessage = new
            System.Net.Mail.MailMessage(from, to, subject, body);

            MyMailMessage.IsBodyHtml = false;

            System.Net.NetworkCredential mailAuthentication = new
            System.Net.NetworkCredential(username, password);

            System.Net.Mail.SmtpClient mailClient = new
            System.Net.Mail.SmtpClient("smtp.gmail.com", 587); //gmail 

            mailClient.EnableSsl = true;
            mailClient.UseDefaultCredentials = false;
            mailClient.Credentials = mailAuthentication;
            mailClient.Send(MyMailMessage);
        }

        //Mã hóa MẬT KHẨU
        public static string MaHoaMatKhau(string pass)
        {
            System.Text.Encoding enc = System.Text.Encoding.ASCII;
            byte[] myByteArray = enc.GetBytes(pass);
            return enc.GetString(SHA1.Create().ComputeHash(myByteArray));
        }


        //Backup số liệu
        public static bool Backup(string Path)
        {
            string DataBase = System.Windows.Forms.Application.StartupPath + "\\Data\\QLKS.mdf";
            string cmd = string.Format("Use master BACKUP DATABASE [{0}] TO  DISK =N'{1}' ", DataBase, Path);
            try
            {
                RunExecute(cmd);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "QLKS", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                return false;
            }
            return true;
        }

        //Restore số liệu
        public static bool Restore(string InputPath, string OutPath)
        {
            try
            {
                DataTable tb = QueryResults(string.Format("RESTORE FILELISTONLY FROM DISK='{0}'", InputPath)).Tables[0];

                string acc_book = tb.Rows[0]["LogicalName"].ToString().Trim();
                string acc_book_log = tb.Rows[1]["LogicalName"].ToString().Trim();

                string cmd = string.Format("Use master RESTORE DATABASE [{0}] FROM  DISK =N'{1}' WITH MOVE '{2}' To '{3}', MOVE '{4}' To '{5}'", "QLKS", InputPath, acc_book, OutPath + "\\QLKS.mdf", acc_book_log, OutPath + "\\QLKS.ldf");
            
                    RunExecute(cmd);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "QLKS", MessageBoxButton.OK, MessageBoxImage.Asterisk);
                return false;
            }
            return true;
        }

        #endregion
    }
    #endregion
}
